package zy.dao.sell.card.impl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.sell.card.CardDAO;
import zy.entity.sell.card.T_Sell_Card;
import zy.entity.sell.card.T_Sell_CardList;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.NumberUtil;
import zy.util.StringUtil;

@Repository
public class CardDAOImpl extends BaseDaoImpl implements CardDAO{

	@Override
	public Integer count(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object cd_shop_code = params.get("cd_shop_code");
		Object cd_cardcode = params.get("cd_cardcode");
		Object cd_name = params.get("cd_name");
		Object cd_mobile = params.get("cd_mobile");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_sell_card t");
		sql.append(" JOIN t_base_shop sp ON sp_code = cd_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND cd_grantdate >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND cd_grantdate <= :enddate ");
		}
		if (StringUtil.isNotEmpty(cd_shop_code)) {
			sql.append(" AND cd_shop_code = :cd_shop_code ");
		}
		if (StringUtil.isNotEmpty(cd_cardcode)) {
			sql.append(" AND INSTR(cd_cardcode, :cd_cardcode) > 0 ");
		}
		if (StringUtil.isNotEmpty(cd_name)) {
			sql.append(" AND INSTR(cd_name, :cd_name) > 0 ");
		}
		if (StringUtil.isNotEmpty(cd_mobile)) {
			sql.append(" AND INSTR(cd_mobile, :cd_mobile) > 0 ");
		}
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(" AND sp_upcode = :shop_code AND (sp_shop_type = "+CommonUtil.THREE+" OR sp_shop_type = "+CommonUtil.FIVE+")");
		}else if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FOUR.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、加盟店、合伙店
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Sell_Card> list(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object cd_shop_code = params.get("cd_shop_code");
		Object cd_cardcode = params.get("cd_cardcode");
		Object cd_name = params.get("cd_name");
		Object cd_mobile = params.get("cd_mobile");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT cd_id,cd_code,cd_cardcode,cd_shop_code,cd_grantdate,cd_enddate,cd_money,cd_realcash,cd_bankmoney,cd_used_money,");
		sql.append(" cd_cashrate,cd_state,cd_manager,cd_name,cd_mobile,cd_idcard,cd_pass,cd_verify_code,t.companyid,");
		sql.append(" sp_name AS shop_name");
		sql.append(" FROM t_sell_card t");
		sql.append(" JOIN t_base_shop sp ON sp_code = cd_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND cd_grantdate >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND cd_grantdate <= :enddate ");
		}
		if (StringUtil.isNotEmpty(cd_shop_code)) {
			sql.append(" AND cd_shop_code = :cd_shop_code ");
		}
		if (StringUtil.isNotEmpty(cd_cardcode)) {
			sql.append(" AND INSTR(cd_cardcode, :cd_cardcode) > 0 ");
		}
		if (StringUtil.isNotEmpty(cd_name)) {
			sql.append(" AND INSTR(cd_name, :cd_name) > 0 ");
		}
		if (StringUtil.isNotEmpty(cd_mobile)) {
			sql.append(" AND INSTR(cd_mobile, :cd_mobile) > 0 ");
		}
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(" AND sp_upcode = :shop_code AND (sp_shop_type = "+CommonUtil.THREE+" OR sp_shop_type = "+CommonUtil.FIVE+")");
		}else if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FOUR.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、加盟店、合伙店
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY cd_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sell_Card.class));
	}

	@Override
	public Integer countDetail(Map<String, Object> params) {
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object cdl_shop_code = params.get("cdl_shop_code");
		Object cdl_cardcode = params.get("cdl_cardcode");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_sell_cardlist t");
		sql.append(" WHERE 1 = 1");
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND cdl_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND cdl_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(cdl_shop_code)) {
			sql.append(" AND cdl_shop_code = :cdl_shop_code ");
		}
		if (StringUtil.isNotEmpty(cdl_cardcode)) {
			sql.append(" AND INSTR(cdl_cardcode, :cdl_cardcode) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Sell_CardList> listDetail(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object cdl_shop_code = params.get("cdl_shop_code");
		Object cdl_cardcode = params.get("cdl_cardcode");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT cdl_id,cdl_cd_code,cdl_cardcode,cdl_money,cdl_realcash,cdl_bankmoney,cdl_date,cdl_type,cdl_shop_code,cdl_ba_code,cdl_bank_code,cdl_number,cdl_manager,companyid,");
		sql.append(" (SELECT sp_name FROM t_base_shop sp WHERE sp_code = cdl_shop_code AND sp.companyid = t.companyid LIMIT 1) AS shop_name,");
		sql.append(" (SELECT ba_name FROM t_money_bank ba WHERE ba_code = cdl_ba_code AND ba.companyid = t.companyid LIMIT 1) AS ba_name,");
		sql.append(" (SELECT ba_name FROM t_money_bank ba WHERE ba_code = cdl_bank_code AND ba.companyid = t.companyid LIMIT 1) AS bank_name");
		sql.append(" FROM t_sell_cardlist t");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND cdl_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND cdl_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(cdl_shop_code)) {
			sql.append(" AND cdl_shop_code = :cdl_shop_code ");
		}
		if (StringUtil.isNotEmpty(cdl_cardcode)) {
			sql.append(" AND INSTR(cdl_cardcode, :cdl_cardcode) > 0 ");
		}
		sql.append(" AND companyid = :companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY cdl_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params,new BeanPropertyRowMapper<>(T_Sell_CardList.class));
	}
	
	@Override
	public List<T_Sell_CardList> listDetail(String cd_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT cdl_id,cdl_cd_code,cdl_cardcode,cdl_money,cdl_realcash,cdl_bankmoney,cdl_date,cdl_type,cdl_shop_code,cdl_ba_code,cdl_bank_code,cdl_number,cdl_manager,companyid,");
		sql.append(" (SELECT sp_name FROM t_base_shop sp WHERE sp_code = cdl_shop_code AND sp.companyid = t.companyid LIMIT 1) AS shop_name,");
		sql.append(" (SELECT ba_name FROM t_money_bank ba WHERE ba_code = cdl_ba_code AND ba.companyid = t.companyid LIMIT 1) AS ba_name,");
		sql.append(" (SELECT ba_name FROM t_money_bank ba WHERE ba_code = cdl_bank_code AND ba.companyid = t.companyid LIMIT 1) AS bank_name");
		sql.append(" FROM t_sell_cardlist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND cdl_cd_code = :cdl_cd_code");
		sql.append(" AND companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), new MapSqlParameterSource().addValue("cdl_cd_code", cd_code).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Sell_CardList.class));
	}

	@Override
	public Integer countByCardCode(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_sell_card");
		sql.append(" WHERE 1=1");
		sql.append(" AND cd_cardcode IN(:cd_cardcode)");
		sql.append(" AND companyid = :companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public T_Sell_Card queryByID(Integer cd_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT cd_id,cd_code,cd_cardcode,cd_shop_code,cd_grantdate,cd_enddate,cd_money,cd_realcash,cd_bankmoney,cd_used_money,");
		sql.append(" cd_cashrate,cd_state,cd_manager,cd_name,cd_mobile,cd_idcard,cd_pass,cd_verify_code,companyid,");
		sql.append(" (SELECT sp_name FROM t_base_shop sp WHERE sp_code = cd_shop_code AND sp.companyid = t.companyid LIMIT 1) AS shop_name");
		sql.append(" FROM t_sell_card t");
		sql.append(" WHERE cd_id=:cd_id");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("cd_id", cd_id),
					new BeanPropertyRowMapper<>(T_Sell_Card.class));
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public Integer checkExistOperation(String cd_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT cdl_id");
		sql.append(" FROM t_sell_cardlist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND cdl_type != 0");
		sql.append(" AND cdl_cd_code = :cdl_cd_code");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("cdl_cd_code", cd_code).addValue("companyid", companyid),Integer.class);
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public T_Sell_CardList queryInitBank(String cd_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT cdl_ba_code,cdl_bank_code");
		sql.append(" FROM t_sell_cardlist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND cdl_type = 0");
		sql.append(" AND cdl_cd_code = :cdl_cd_code");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("cdl_cd_code", cd_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Sell_CardList.class));
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public void save(List<T_Sell_Card> cards, List<T_Sell_CardList> cardDetails) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT f_autoincre_code(max(cd_code+0)) FROM t_sell_card ");
		sql.append(" WHERE 1=1");
		sql.append(" AND companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(cards.get(0)), String.class);
		int beginCode = Integer.parseInt(code);
		for (int i = 0; i < cards.size(); i++) {
			cards.get(i).setCd_code(StringUtil.trimString(beginCode+i));
			cardDetails.get(i).setCdl_cd_code(StringUtil.trimString(beginCode+i));
		}
		sql.setLength(0);
		sql.append(" INSERT INTO t_sell_card");
		sql.append(" (cd_code,cd_cardcode,cd_shop_code,cd_grantdate,cd_enddate,cd_money,cd_realcash,cd_bankmoney,cd_used_money,");
		sql.append(" cd_cashrate,cd_state,cd_manager,cd_name,cd_mobile,cd_idcard,cd_pass,cd_verify_code,companyid)");
		sql.append(" VALUES");
		sql.append(" (:cd_code,:cd_cardcode,:cd_shop_code,:cd_grantdate,:cd_enddate,:cd_money,:cd_realcash,:cd_bankmoney,:cd_used_money,");
		sql.append(" :cd_cashrate,:cd_state,:cd_manager,:cd_name,:cd_mobile,:cd_idcard,:cd_pass,:cd_verify_code,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(cards.toArray()));
		sql.setLength(0);
		sql.append(" INSERT INTO t_sell_cardlist");
		sql.append(" (cdl_cd_code,cdl_cardcode,cdl_money,cdl_realcash,cdl_bankmoney,cdl_date,cdl_type,cdl_shop_code,cdl_ba_code,cdl_bank_code,cdl_number,cdl_manager,companyid)");
		sql.append(" VALUES");
		sql.append(" (:cdl_cd_code,:cdl_cardcode,:cdl_money,:cdl_realcash,:cdl_bankmoney,:cdl_date,:cdl_type,:cdl_shop_code,:cdl_ba_code,:cdl_bank_code,:cdl_number,:cdl_manager,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(cardDetails.toArray()));
	}
	
	@Override
	public void saveDetail(T_Sell_CardList cardList) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" INSERT INTO t_sell_cardlist");
		sql.append(" (cdl_cd_code,cdl_cardcode,cdl_money,cdl_realcash,cdl_bankmoney,cdl_date,cdl_type,cdl_shop_code,cdl_ba_code,cdl_bank_code,cdl_number,cdl_manager,companyid)");
		sql.append(" VALUES");
		sql.append(" (:cdl_cd_code,:cdl_cardcode,:cdl_money,:cdl_realcash,:cdl_bankmoney,:cdl_date,:cdl_type,:cdl_shop_code,:cdl_ba_code,:cdl_bank_code,:cdl_number,:cdl_manager,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(cardList),holder);
		cardList.setCdl_id(holder.getKey().intValue());
	}

	@Override
	public void update(T_Sell_Card card) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sell_card");
		sql.append(" SET cd_name=:cd_name");
		sql.append(" ,cd_mobile=:cd_mobile");
		sql.append(" ,cd_idcard=:cd_idcard");
		sql.append(" ,cd_enddate=:cd_enddate");
		sql.append(" WHERE cd_id=:cd_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(card));
	}
	
	@Override
	public void updateMoney(T_Sell_Card card) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sell_card");
		sql.append(" SET cd_money=:cd_money");
		sql.append(" ,cd_realcash=:cd_realcash");
		sql.append(" ,cd_bankmoney=:cd_bankmoney");
		sql.append(" ,cd_cashrate=:cd_cashrate");
		sql.append(" WHERE cd_id=:cd_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(card));
	}

	@Override
	public void updateState(T_Sell_Card card) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sell_card");
		sql.append(" SET cd_state = :cd_state");
		sql.append(" WHERE cd_id=:cd_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(card));
	}

	@Override
	public void del(Integer cd_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sell_card");
		sql.append(" WHERE cd_id=:cd_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("cd_id", cd_id));
	}

	@Override
	public void delList(String cd_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sell_cardlist");
		sql.append(" WHERE cdl_cd_code=:cd_code AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("cd_code", cd_code).addValue("companyid", companyid));
	}
//-----------------前台--------------------

	@Override
	public List<T_Sell_Card> listShop(Map<String, Object> param) {
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		Object shop_uptype = param.get(CommonUtil.SHOP_UPTYPE);
		Object cd_cardcode = param.get("cd_cardcode");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT cd_id,cd_code,cd_cardcode,cd_grantdate,cd_enddate,cd_money,cd_realcash,cd_bankmoney,cd_used_money,");
		sql.append(" cd_state,cd_manager,cd_name,cd_mobile,cd_idcard,");
		sql.append(" sp_name AS shop_name");
		sql.append(" FROM t_sell_card t");
		sql.append(" JOIN t_base_shop sp ON sp_code = cd_shop_code AND sp.companyid = t.companyid");
		if(!CommonUtil.FOUR.equals(shop_type)){ // 如果是自营店，则下上级店铺相同所有会员
			sql.append(getSellShopSQL(shop_uptype, 0));
		}else{
			sql.append(" AND sp_code=:shop_code");
		} 
		sql.append(" WHERE 1 = 1");
		if (StringUtil.isNotEmpty(cd_cardcode)) {
			sql.append(" AND cd_cardcode=:cd_cardcode");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Sell_Card.class));
	}

	
	
	@Override
	public Integer idByCode(Map<String, Object> param) {
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		Object shop_uptype = param.get(CommonUtil.SHOP_UPTYPE);
		Object cd_cardcode = param.get("cd_cardcode");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT cd_id");
		sql.append(" FROM t_sell_card t");
		sql.append(" JOIN t_base_shop sp ON sp_code = cd_shop_code AND sp.companyid = t.companyid");
		if(!CommonUtil.FOUR.equals(shop_type)){ // 如果是自营店，则下上级店铺相同所有会员
			sql.append(getSellShopSQL(shop_uptype, 0));
		}else{
			sql.append(" AND sp_code=:shop_code");
		} 
		sql.append(" WHERE 1 = 1");
		if (StringUtil.isNotEmpty(cd_cardcode)) {
			sql.append(" AND cd_cardcode=:cd_cardcode");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" LIMIT 1");
		try {
			Integer id = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
			return id;
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public void saveShop(Map<String, Object> param) {
		T_Sell_Card card = (T_Sell_Card)param.get("card");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT f_autoincre_code(max(cd_code+0)) FROM t_sell_card ");
		sql.append(" WHERE 1=1");
		sql.append(" AND companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(card), String.class);
		card.setCd_code(code);
		sql.setLength(0);
		sql.append(" INSERT INTO t_sell_card");
		sql.append(" (cd_code,cd_cardcode,cd_shop_code,cd_grantdate,cd_enddate,cd_money,cd_realcash,cd_bankmoney,cd_used_money,");
		sql.append(" cd_cashrate,cd_state,cd_manager,cd_name,cd_mobile,cd_idcard,cd_pass,companyid)");
		sql.append(" VALUES");
		sql.append(" (:cd_code,:cd_cardcode,:cd_shop_code,:cd_grantdate,:cd_enddate,:cd_money,:cd_realcash,:cd_bankmoney,:cd_used_money,");
		sql.append(" :cd_cashrate,:cd_state,:cd_manager,:cd_name,:cd_mobile,:cd_idcard,:cd_pass,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(card),holder);
		card.setCd_id(holder.getKey().intValue());
		sql.setLength(0);
		sql.append(" INSERT INTO t_sell_cardlist");
		sql.append(" (cdl_cd_code,cdl_cardcode,cdl_money,cdl_realcash,cdl_bankmoney,cdl_date,cdl_type,cdl_shop_code,cdl_ba_code,cdl_bank_code,cdl_manager,companyid)");
		sql.append(" VALUES");
		sql.append(" (:cd_code,:cd_cardcode,:cd_money,:cd_realcash,:cd_bankmoney,sysdate(),0,:cd_shop_code,:cdl_ba_code,:cdl_bank_code,:cd_manager,:companyid)");
		try{
			namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(card));
		}catch(Exception e){
			e.printStackTrace();
		}
	}

	@Override
	public void editPass(Map<String, Object> paramMap) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT CD_ID ");
		sql.append(" FROM t_sell_card");
		sql.append(" WHERE cd_pass=:old_pass");
		sql.append(" AND cd_id=:cd_id");
		Integer id = null;
		try {
			id = namedParameterJdbcTemplate.queryForObject(sql.toString(), paramMap, Integer.class);
		} catch (Exception e) {
			throw new IllegalArgumentException("旧密码不正确");
		}
		if(null != id){
			sql.setLength(0);
			sql.append(" UPDATE t_sell_card");
			sql.append(" SET cd_pass=:new_pass");
			sql.append(" WHERE cd_id=:cd_id");
			namedParameterJdbcTemplate.update(sql.toString(),paramMap);
		}else{
			throw new IllegalArgumentException("旧密码不正确");
		}
	}

	@Override
	public T_Sell_Card queryByCode(Map<String, Object> param) {
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		Object shop_uptype = param.get(CommonUtil.SHOP_UPTYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT cd_id,cd_code,cd_cardcode,cd_money,cd_realcash,cd_cashrate");
		sql.append(" FROM t_sell_card t");
		sql.append(" JOIN t_base_shop sp ON sp_code = cd_shop_code AND sp.companyid = t.companyid");
		if(!CommonUtil.FOUR.equals(shop_type)){ // 如果是自营店，则下上级店铺相同所有会员
			sql.append(getSellShopSQL(shop_uptype, 0));
		}else{
			sql.append(" AND sp_code=:shop_code");
		} 
		sql.append(" WHERE 1 = 1");
		sql.append(" AND cd_enddate >= sysdate()");
		sql.append(" AND cd_cardcode=:cd_cardcode");
		sql.append(" AND cd_pass=:cd_pass");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, new BeanPropertyRowMapper<>(T_Sell_Card.class));
		} catch (Exception e) {
			return null;
		}
	}
	

	@Override
	public T_Sell_Card cardByMobile(Map<String, Object> param) {
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		Object shop_uptype = param.get(CommonUtil.SHOP_UPTYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT cd_name,cd_cardcode,cd_money");
		sql.append(" FROM t_sell_card t");
		sql.append(" JOIN t_base_shop sp ON sp_code = cd_shop_code AND sp.companyid = t.companyid");
		if(!CommonUtil.FOUR.equals(shop_type)){ // 如果是自营店，则下上级店铺相同所有会员
			sql.append(getSellShopSQL(shop_uptype, 0));
		}else{
			sql.append(" AND sp_code=:shop_code");
		} 
		sql.append(" WHERE 1 = 1");
		sql.append(" AND (cd_cardcode=:mobile OR cd_mobile=:mobile)");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, new BeanPropertyRowMapper<>(T_Sell_Card.class));
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public void updateCard(Map<String, Object> param) {
		String[] cd_ids = (String[])param.get("cd_ids");
		String[] cd_money = (String[])param.get("cd_money");
		String number = (String)param.get("number");
		String emp_name = (String)param.get(CommonUtil.EMP_NAME);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT cd_id,cd_code,cd_cardcode,cd_money,cd_used_money,cd_realcash,cd_cashrate");
		sql.append(",cd_shop_code,companyid");
		sql.append(" FROM t_sell_card t");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND cd_id IN (:_cd_ids)");
		param.put("_cd_ids", Arrays.asList(cd_ids));
		List<T_Sell_Card> list = namedParameterJdbcTemplate.query(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Sell_Card.class));
		if(null != list && list.size() > 0){
			List<T_Sell_CardList> cardList = new ArrayList<T_Sell_CardList>();
			for(T_Sell_Card card:list){
				for(int i = 0;i<cd_ids.length;i++){
					Double money = NumberUtil.toDouble(StringUtil.trimString(cd_money[i]));
					card.setCd_used_money(card.getCd_used_money()+money);
					card.setCd_money(card.getCd_money()-money);
					T_Sell_CardList _card = new T_Sell_CardList();
					_card.setCdl_money(-money);
					_card.setCdl_realcash(money*card.getCd_cashrate());
					_card.setCdl_cardcode(card.getCd_cardcode());
					_card.setCdl_cd_code(card.getCd_code());
					_card.setCdl_date(DateUtil.getYearMonthDate());
					_card.setCdl_number(number);
					_card.setCdl_manager(emp_name);
					_card.setCdl_shop_code(card.getCd_shop_code());
					_card.setCompanyid(card.getCompanyid());
					_card.setCdl_type(3);//消费
					cardList.add(_card);
				}
			}
			sql.setLength(0);
			sql.append(" UPDATE t_sell_card SET");
			sql.append(" cd_used_money=:cd_used_money");
			sql.append(" ,cd_money=:cd_money");
			sql.append(" WHERE 1=1 ");
			sql.append(" AND cd_id=:cd_id");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(list.toArray()));
			sql.setLength(0);
			sql.append(" INSERT INTO t_sell_cardlist(");
			sql.append(" cdl_cd_code,cdl_cardcode,cdl_money,cdl_realcash,");
			sql.append(" cdl_number,cdl_manager,cdl_date,cdl_type,");
			sql.append(" cdl_shop_code,companyid");
			sql.append(" ) VALUES (");
			sql.append(" :cdl_cd_code,:cdl_cardcode,:cdl_money,:cdl_realcash,");
			sql.append(" :cdl_number,:cdl_manager,");
			sql.append(" :cdl_date,:cdl_type,:cdl_shop_code,:companyid");
			sql.append(" )");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(cardList.toArray()));
		}
	}
	
}
